<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>MySQL Ruby tutorial</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Ruby, MySQL, language, database, MySQLdb">
<meta name="description" content="This is MySQL Ruby tutorial. It covers the basics of
programming MySQL in Ruby language.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

<script type="text/javascript">
  (function() {
    var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
    po.src = 'https://apis.google.com/js/plusone.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
  })();
</script>

<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-5536206-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>
</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<div class="content2">


<a href="/" title="Home">Home</a>


<h1>MySQL Ruby tutorial</h1>


<p>
This is a Ruby programming tutorial for the MySQL database. It covers the basics 
of MySQL programming with Ruby.
It uses the mysql2 module. The examples were created and tested on Ubuntu Linux. 
</p>

<p>
There is a similar <a href="/tutorials/mysqlcapitutorial/">MySQL C API tutorial</a>, 
<a href="/db/mysqlvb/">MySQL Visual Basic tutorial</a>, 
or <a href="/db/mysqlpython/">MySQL Python tutorial</a> on ZetCode.
</p>

<p>
You may also consider 
to look at the <a href="/databases/mysqltutorial/">MySQL tutorial</a>, too.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>MySQL &amp; Ruby</h2>

<p>
<b>MySQL</b> is a leading open source database management system. It is a multi user, 
multithreaded database management system. MySQL is especially popular on the web. 
It is one of the parts of the very popular <b>LAMP</b>
platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle.
MySQL database is available on most important OS
platforms. 
<b>Ruby</b> is a dynamic, reflective, general-purpose object-oriented programming language.
Recently it became very popular in web programming, mainly due to the successful
Ruby on Rails framework.
</p>


<h2>mysql2 module</h2>

<p>
The mysql2 module is a Ruby interface to the MySQL server. 
It provides the same functions for Ruby programs that the 
MySQL C API provides for C programs.
</p>


<pre>
$ sudo gem1.9 install mysql2
</pre>

<p>
Here we install the Ruby module for the MySQL database. 
</p>

<h2>Before we start</h2>

<p> 
We are going to create a new database user and a new database. 
To do this, we use the mysql client program.
</p>
 
<pre>
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)
</pre> 
 
<p> 
We connect to the MySQL server using the root account. We show all available
databases with the SHOW DATABASES statement. 
</p> 
 
<pre>
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.02 sec)
</pre> 
 
<p> 
We create a new <b>mydb</b> database. We will use this database throughout 
the tutorial.
</p> 
 
<pre>
mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';
Query OK, 0 rows affected (0.00 sec)
 
mysql> USE mydb;
Database changed
 
mysql> GRANT ALL ON mydb.* to user12@localhost;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye
</pre> 
 
<p> 
We create a new database user. We grant all privileges to this user
for all tables of the mydb database. 
</p> 


<h2>MySQL server version</h2>

<p>
In the first example, we will get the version of
the MySQL database. 
</p>


<pre class="code">
#!/usr/bin/ruby

require 'mysql'


begin
    con = Mysql.new 'localhost', 'user12', '34klq*'
    puts con.get_server_info
    rs = con.query 'SELECT VERSION()'
    puts rs.fetch_row    
    
rescue Mysql::Error => e
    puts e.errno
    puts e.error
    
ensure
    con.close if con
end
</pre>

<p>
In this script, we get the server version. We do it in two different ways. 
</p>


<pre class="explanation">
require 'mysql'
</pre>

<p>
We import the <code>mysql</code> module. The module has the classes and
methods to work with the MySQL database. 
</p>

<pre class="explanation">
con = Mysql.new 'localhost', 'user12', '34klq*'
</pre>

<p>
We create the connection object. The parameters include the host name, user name
and password. In our case the host name is localhost, e.g. our computer.
</p>

<pre class="explanation">
puts con.get_server_info
</pre>

<p>
The Mysql object, that we have created, has a <code>get_server_info</code> method.
It returns the version of the MySQL server installed. 
</p>

<pre class="explanation">
rs = con.query 'SELECT VERSION()'
puts rs.fetch_row    
</pre>

<p>
Another way to get the version is to execute the <code>SELECT VERSION()</code>
SQL statement. We fetch the data. Since we retrieve only one record, we
call the <code>fetch_row</code> method.
</p>

<pre class="explanation">
rescue Mysql::Error => e
    puts e.errno
    puts e.error
</pre>

<p>
We check for errors. This is important, since working with databases is
error prone. 
</p>

<pre class="explanation">
ensure
    con.close if con
end
</pre>

<p>
In the end, we release the resources. 
</p>

<pre>
$ ./version.rb
5.5.9
5.5.9
</pre>

<p>
The output might look like the above. 
</p>


<h2>Listing databases</h2>

<p>
The MySQL Ruby module has a <code>list_dbs</code> method, which returns
available databases. 
</p>


<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*'

    con.list_dbs.each do |db|
        puts db
    end
    
rescue Mysql::Error => e
    puts e.errno
    puts e.error
    
ensure
    con.close if con
end
</pre>

<p>
In this script, we print all available databases 
on our MySQL server. 
</p>

<pre class="explanation">
con.list_dbs.each do |db|
    puts db
end
</pre>

<p>
The <code>list_dbs</code> method returns an array of available database
names. Using the each method of the array, we print each item 
of the array to the console.
</p>

<pre>
$ ./listdb.rb
information_schema
mydb
test
world
</pre>

<p>
On my system, I had the above databases created. 
</p>


<h2>Creating and populating a table</h2>

<p>
We create a table and populate it with some data.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
   
    con.query("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
    con.query("INSERT INTO Writers(Name) VALUES('Jack London')")
    con.query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    con.query("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    con.query("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    con.query("INSERT INTO Writers(Name) VALUES('Truman Capote')")   
    
rescue Mysql::Error => e
    puts e.errno
    puts e.error
    
ensure
    con.close if con
end
</pre>

<p>
We create a Writers table and add five authors to it. 
</p>

<pre class="explanation">
con.query("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
</pre>

<p>
To execute an SQL statement, we use the <code>query</code> method.
This SQL statement creates a new database table called Writers. It has
two columns. Id and Name. 
</p>


<pre class="explanation">
con.query("INSERT INTO Writers(Name) VALUES('Jack London')")
con.query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...
</pre>

<p>
We use the <code>INSERT</code> statement to insert authors to the table. Here
we add two rows. 
</p>

<pre>
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)
</pre>

<p>
After executing the script, we use the <code>mysql</code> client tool to 
select all data from the Writers table. 
</p>


<h2>Retrieving data</h2>

<p>
Now, that we have inserted some data into the database, we can retrieve it back.
</p>

<pre class="code">
#!/usr/bin/ruby

require "mysql"

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    rs = con.query("SELECT * FROM Writers")
    n_rows = rs.num_rows
    
    puts "There are #{n_rows} rows in the result set"
    
    n_rows.times do
        puts rs.fetch_row.join("\s")
    end
 
rescue Mysql::Error => e
    puts e.errno
    puts e.error
    
ensure
    con.close if con
end
</pre>

<p>
In this example, we retrieve all data from the Writers table. 
</p>

<pre class="explanation">
con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
</pre>

<p>
The last parameter of the constructor is the database name, to 
which we connect. 
</p>


<pre class="explanation">
rs = con.query("SELECT * FROM Writers")
</pre>

<p>
This SQL statement selects all data from the Writers
table. 
</p>

<pre class="explanation">
n_rows = rs.num_rows
</pre>

<p>
We get the number of rows in the result set using the <code>num_rows</code>
method of the result set object. 
</p>

<pre class="explanation">
n_rows.times do
    puts rs.fetch_row.join("\s")
end
</pre>

<p>
Here we fetch each row with the <code>fetch_row</code> method. It returns
a row as an array of fields. By default, the fields are separated by new line, when
printed. With the <code>join</code> method, we print each row on one line. The fields
are separated by one space. 
</p>


<pre>
$ ./retrieve1.rb
There are 5 rows in the result set
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote
</pre>

<hr class="btm">

<p>
Next we present another way to retrieve data from the table. 
</p>

<pre class="code">
#!/usr/bin/ruby

require "mysql"

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    rs = con.query("SELECT * FROM Writers")
    
    rs.each do |row|
        puts row.join("\s")
    end
        
rescue Mysql::Error => e
    puts e.errno
    puts e.error
    
ensure
    con.close if con
end
</pre>

<p>
We print all data from the Writers table. This time we use
an each method of the result set to traverse the data. 
</p>

<pre class="explanation">
rs.each do |row|
    puts row.join("\s")
end
</pre>

<p>
We iterate through the result set using the each method. 
</p>

<pre>
$ ./retrieve2.rb
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote
</pre>


<p>
This is the output of the example. 
</p>

<hr class="btm">

<p>
We can traverse data in form of a Ruby hash. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
    puts "We have #{rs.num_rows} row(s)"
    
    rs.each_hash do |row|
       puts row['Id'] + " " + row['Name']
    end      
    
rescue Mysql::Error => e
    puts e    
ensure
    con.close if con
end
</pre>

<p>
In the example, we use the <code>each_hash</code> iterator. Records from
the result set can be retrieved by their column names. 
</p>

<pre class="explanation">
rs.each_hash do |row|
    puts row['Id'] + " " + row['Name']
end     
</pre>

<p>
We go through the result set with the <code>each_hash</code> method. 
Each returned row is a Ruby hash; a collection of key-value pairs. The keys
are the column names. 
</p>

<pre>
$ ./retrieve3.rb
We have 3 row(s)
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
</pre>

<p>
Ouput of the example. 
</p>

<h2>Multiple statements</h2>

<p>
MySQL supports multiple statement execution. This must be enabled by a special 
option. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'


begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
    con.set_server_option Mysql::OPTION_MULTI_STATEMENTS_ON

    rs = con.query "SELECT Name FROM Writers WHERE Id=1;
                    SELECT Name FROM Writers WHERE Id=2;
                    SELECT Name FROM Writers WHERE Id=3"

    puts rs.fetch_row        
            
    while con.next_result
        rs = con.store_result    
        puts rs.fetch_row
    end       
         
rescue Mysql::Error => e
    puts e.errno
    puts e.error
    
ensure
    con.close if con
end
</pre>

<p>
In this example, we have three SELECT statements in one query. 
</p>

<pre class="explanation">
con.set_server_option Mysql::OPTION_MULTI_STATEMENTS_ON
</pre>

<p>
First we need to enable the multiple statements processing 
with the <code>Mysql::OPTION_MULTI_STATEMENTS_ON</code>.
</p>

<pre class="explanation">
rs = con.query "SELECT Name FROM Writers WHERE Id=1;
                SELECT Name FROM Writers WHERE Id=2;
                SELECT Name FROM Writers WHERE Id=3"
</pre>

<p>
Here we define three SELECT statements. They are separated by a semicolon. 
</p>

<pre class="explanation">
puts rs.fetch_row 
</pre>

<p>
The query method returns the first result set. We fetch a row from this result
set. 
</p>

<pre class="explanation">
while con.next_result
    rs = con.store_result    
    puts rs.fetch_row
end   
</pre>

<p>
We get additional result sets until there are no more statements left to 
process. 
</p>

<pre>
$ ./multiplest.rb
Jack London
Honore de Balzac
Lion Feuchtwanger
</pre>

<p>
Running the example. 
</p>


<h2>Metadata</h2>

<p>
Metadata is information about the data in the database. Metadata in a MySQL
system contains information about the tables and columns, in which we store
data. Number of rows affected by an SQL statement is a metadata. Number of 
rows and columns returned in a result set belong to metadata as well. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
    puts "We have #{con.field_count} fields"
    puts "We have #{rs.num_rows} row(s)"
    puts rs.fetch_row.join("\s")
    
rescue Mysql::Error => e
    puts e    
ensure
    con.close if con
end
</pre>

<p>
In this script, we find out the number of rows and columns from 
an SQL query. 
</p>

<pre class="explanation">
rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
</pre>

<p>
This SQL statement returns three rows. Each row has two columns. 
</p>

<pre class="explanation">
puts "We have #{con.field_count} fields"
puts "We have #{rs.num_rows} row(s)"
</pre>

<p>
These two lines return the number of columns and rows in the result set.
Note that here a field is a synonym for a column. The returned data
is metadata. 
</p>

<pre class="explanation">
puts rs.fetch_row.join("\s")
</pre>

<p>
Here we return one row from the result set. This is the original data stored
in our database table. 
</p>

<hr class="btm">

<p>
For <code>INSERT</code>, <code>DELETE</code> and <code>UPDATE</code> statements
there is a method called <code>rows_affected</code>. This method returns the number
of rows affected by these three statements. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    con.query "DELETE FROM Writers WHERE Id IN (1, 2, 3)"
    
    puts "The query has affected #{con.affected_rows} rows"
    
rescue Mysql::Error => e
    puts e    
ensure
    con.close if con
end
</pre>

<p>
In our example, we delete first three rows from the Writers table.
</p>

<pre class="explanation">
con.query "DELETE FROM Writers WHERE Id IN (1, 2, 3)"
</pre>

<p>
An SQL statement, which deletes first three rows of the Writers table. 
</p>

<pre class="explanation">
puts "The query has affected #{con.affected_rows} rows"
</pre>

<p>
Here we get the number of rows, that were affected by the above SQL statement.
This number belongs to the metadata. 
</p>

<pre>
$ ./affected.rb
The query has affected 3 rows

mysql> SELECT * FROM Writers;
+----+---------------+
| Id | Name          |
+----+---------------+
|  4 | Emile Zola    |
|  5 | Truman Capote |
+----+---------------+
2 rows in set (0.00 sec)
</pre>

<p>
We execute the affected.rb script and check for changes in the Writers table. 
Three rows have been deleted. 
</p>

<hr class="btm">

<p>
In the next example, we are going to check for metadata about a field. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'


begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
    rs = con.query "SELECT * FROM Writers WHERE Id=1"
    field = rs.fetch_field_direct 1
    
    puts "Table name: #{field.table}"
    puts "Field name: #{field.name}"
    puts "Field length: #{field.length}"
    puts "Field type: #{field.type}"
    
rescue Mysql::Error => e
    puts e
    
ensure
    con.close if con
end
</pre>

<p>
We get one record from the database. We get the field's table name,
colum name, length and type. 
</p>

<pre class="explanation">
rs = con.query "SELECT * FROM Writers WHERE Id=1"
</pre>

<p>
This query returns one row. It has two columns. 
</p>

<pre class="explanation">
field = rs.fetch_field_direct 1
</pre>

<p>
Using the <code>fetch_field_direct</code> method, we get 
a specific record. More precisely, the record from the 
intersection of the first row, second column. 
</p>

<pre class="explanation">
puts "Table name: #{field.table}"
puts "Field name: #{field.name}"
puts "Field length: #{field.length}"
puts "Field type: #{field.type}"
</pre>

<p>
We get the metadata using attribute readers of the field object. 
</p>

<pre>
$ ./metadata.rb
Table name: Writers
Field name: Name
Field length: 25
Field type: 253
</pre>

<p>
This is the output of the example. 
</p>

<hr class="btm">

<p>
In our last example relating to the metadata, we will
print all rows from the table with their column names.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
    rs = con.query "SELECT * FROM Writers"
    
    fields = rs.fetch_fields    
    puts "%3s %s" % [fields[0].name, fields[1].name]
    
    rs.each_hash do |row|
        puts "%3s %s" % [row['Id'], row['Name']]
    end
    
rescue Mysql::Error => e
    puts e
    
ensure
    con.close if con
end
</pre>

<p>
We print the contents of the Writers table to the
console. Now, we include the names of the columns too.
</p>


<pre class="explanation">
fields = rs.fetch_fields    
puts "%3s %s" % [fields[0].name, fields[1].name]
</pre>

<p>
In the first step, we get the column names. They are printed
using the standard Ruby string formatting abilities. 
</p>

<pre class="explanation">
rs.each_hash do |row|
    puts "%3s %s" % [row['Id'], row['Name']]
end
</pre>

<p>
Now the data is fechted and printed to the console. 
We do some formatting too. 
</p>


<pre class="code">
$ ./columnheaders.rb
 Id Name
  1 Jack London
  2 Honore de Balzac
  3 Lion Feuchtwanger
  4 Emile Zola
  5 Truman Capote
</pre>

<p>
Ouput of the script. 
</p>


<h2>Prepared statements</h2>

<p>
Now we will concern ourselves with prepared statements. When we write
prepared statements, we use placeholders instead of directly writing the
values into the statements. Prepared statements increase security and performance. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

name = "Stefan Zweig"

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    pst = con.prepare "INSERT INTO Writers(Name) VALUES(?)"
    pst.execute name
        
rescue Mysql::Error => e
    puts e    
    
ensure
    con.close if con
    pst.close if pst
end
</pre>

<p>
In the above example, we insert a new row into the Writers
table. We use a prepared statement.
</p>

<pre class="explanation">
pst = con.prepare "INSERT INTO Writers(Name) VALUES(?)"
</pre>

<p>
The <code>prepare</code> method is used to create a prepared
statement. The ? character is a placeholder. Later we bind a
value to this placeholder.
</p>

<pre class="explanation">
pst.execute name
</pre>

<p>
We bind a value from the name variable to the placeholder and
execute the prepared statement. 
</p>

<pre class="explanation">
pst.close if pst
</pre>

<p>
The prepared statement is closed. 
</p>

<pre>
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | Stefan Zweig      |
+----+-------------------+
6 rows in set (0.00 sec)
</pre>

<p>
After the script was successfully run, we see a new author in the
Writers table. 
</p>


<h2>Writing images</h2>

<p>
Some people prefer to put their images into the database, some prefer to keep them 
on the file system for their applications. Technical difficulties arise when we 
work with millions of images. Images are binary data. MySQL database has a special 
data type to store binary data called <b>BLOB</b> (Binary Large Object). 
</p>

<pre>
mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)
</pre>

<p>
For this example, we create a new table called Images. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    
    fin = File.open("woman.jpg" , "rb")
    img = fin.read
    
rescue SystemCallError => e      
    puts e
ensure
    fin.close if fin 
end


begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
    pst = con.prepare("INSERT INTO Images SET Data='%s'" % img.unpack('H*'))
    pst.execute    
    
rescue Mysql::Error => e
    puts e
    
ensure
    con.close if con
    pst.close if pst
end
</pre>

<p>
In the above script, we read a jpg image and insert it 
into the Images table. 
</p>

<pre class="explanation">
fin = File.open("woman.jpg" , "rb")
img = fin.read
</pre>

<p>
We open and read an image. The <code>read</code>
method returns the data as string. 
</p>

<pre class="explanation">
pst = con.prepare("INSERT INTO Images SET Data='%s'" % img.unpack('H*'))
</pre>

<p>
This string data is placed into the prepared statement. Before doing so, it is 
decoded using the <code>unpack</code> method of the Ruby string object. 
Decoding is necessary, because the image object has many special characters which cannot
be processed normally.
</p>


<h2>Reading images</h2>

<p>
In the previous example, we have inserted an image into the database table. 
Now we are going to read the image back from the table.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'          
    rs = con.query "SELECT Data FROM Images LIMIT 1"    
    f = File.new "woman2.jpg", "wb"
    f.write rs.fetch_row.pack 'H*'

rescue Mysql::Error, SystemCallError => e
    puts e
    
ensure
    con.close if con
    f.close if f
end
</pre>

<p>
We read one image from the Images table.
</p>


<pre class="explanation">
rs = con.query "SELECT Data FROM Images LIMIT 1" 
</pre>

<p>
We select one record from the table. 
</p>

<pre class="explanation">
f = File.new "woman2.jpg", "wb"
</pre>

<p>
We create a writable binary file. 
</p>

<pre class="explanation">
f.write rs.fetch_row.pack 'H*'
</pre>

<p>
We fetch the data from the previous SQL
statement and write it to the file. The <code>fetch_row</code>
method returns an array object. Before the data is written to the
file, it is put back into the original format with the <code>pack</code>
method of the array. For both operations, decoding and reversing, we use
the same directive, 'H*'. It stands for hex string.
</p>

<p>
Now we should have an image called woman2.jpg in
our current directory. We can check if it is the same
image, that we have inserted into the table. 
</p>

<h2>Transaction support</h2>

<p>
A <code>transaction</code> is an atomic unit of database operations 
against the data in one or more databases. The effects of all the 
SQL statements in a transaction can be either all committed to 
the database or all rolled back.
</p>

<p>
By default, MySQL runs in the autocommit mode. In this mode, all changes to the tables 
are immediately effective. To prevent this, 
we have to turn off the autocommit mode. After disabling autocommit, changes to
transaction-aware tables are not permanent immediately. To store the changes,
we must call the <code>COMMIT</code> statement or <code>ROLLBACK</code>
to revert them. The Ruby MySQL has convenience methods for these SQL statements,
<code>commit</code> and <code>rollback</code>.
</p>

<p>
The MySQL database has different types of storage engines. The most common are the MyISAM
and the InnoDB engines. There is a trade-off between data security and
database speed. The MyISAM tables are faster to process and they do not support transactions.
The <code>commit</code> and <code>rollback</code> methods are not implemented. 
They do nothing. On the other hand, the InnoDB tables are more safe against the data loss. 
They support transactions. They are slower to process. 
</p>

<pre>
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES 
    -> where TABLE_SCHEMA = 'mydb' AND TABLE_NAME='Writers';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| Writers    | InnoDB |
+------------+--------+
1 row in set (0.00 sec)
</pre>

<p>
The engine of the Writers table is InnoDB, which supports transactions.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    con.autocommit false
    
    pst = con.prepare "UPDATE Writers SET Name = ? WHERE Id = ?"
    pst.execute "Leo Tolstoy", "1"
    pst.execute "Boris Pasternak", "2"
    pst.execute "Leonid Leonov"

    con.commit
    
rescue Mysql::Error => e
    puts e
    con.rollback
    
ensure
    pst.close if pst
    con.close if con
end
</pre>

<p>
In this script, we try to update three rows. 
</p>

<pre class="explanation">
con.autocommit false
</pre>

<p>
The autocommit mode is disabled. 
</p>

<pre class="explanation">
pst = con.prepare "UPDATE Writers SET Name = ? WHERE Id = ?"
pst.execute "Leo Tolstoy", "1"
pst.execute "Boris Pasternak", "2"
pst.execute "Leonid Leonov"
</pre>

<p>
We execute three <code>UPDATE</code> statements. The last one
is incorrect. The second parameter is missing. 
</p>

<pre class="explanation">
con.commit 
</pre>

<p>
If all is OK, changes are committed to the table. 
</p>

<pre class="explanation">
rescue Mysql::Error => e
    puts e
    con.rollback
</pre>

<p>
In case of an error, changes are rolled back. 
</p>


<pre>
$ ./update.rb
execute: param_count(2) != number of argument(1)

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | Stefan Zweig      |
+----+-------------------+
6 rows in set (0.00 sec)
</pre>

<p>
Running the script gives an error. However, the transaction
was rolled back and the first two rows were not changed. 
</p>

<p>
This was MySQL Ruby tutorial. 
</p>

<div class="g-plusone"></div>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified September 20, 2011  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
